package com.bringway.p2p.dao.impl;

import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.stereotype.Repository;

import com.bringway.framework.dao.P2PJdbcDao;
import com.bringway.p2p.dao.POSDao;

import net.sf.json.JSONObject;

/*
 * 
 * @ClassName: POSDaoImpl
 * @Description: POSDao持久层实现类
 * @author: 邵jing
 * @date: 2016年2月15日 下午4:05:00
 */
@Repository
public class POSDaoImpl extends P2PJdbcDao implements POSDao {
	
	Logger logger = Logger.getLogger(POSDaoImpl.class);

	/*
	 * (non-Javadoc)
	 * 
	 * @see com.bringway.p2p.dao.POSDao#savePosCode(net.sf.json.JSONObject)
	 */
	@Override
	public int savePosCode(JSONObject parms) {
		StringBuffer sql = new StringBuffer();
		String POS_CODE = parms.optString("POS_CODE");
		String OPER_USER_ID = parms.optString("OPER_USER_ID");
		String POS_OWNER_USER_ID = parms.optString("POS_OWNER_USER_ID");
		String POS_OWNER_SJHM = parms.optString("POS_OWNER_SJHM");
		String POS_CONTENT = parms.optString("POS_CONTENT");
		String POS_BIZ_GYH = parms.optString("POS_BIZ_GYH");
		
		String POS_PLATFORM = parms.optString("POS_PLATFORM");
		String POS_CUSTOMER_SEX = parms.optString("POS_CUSTOMER_SEX");
		String POS_CUSTOMER_SFZ = parms.optString("POS_CUSTOMER_SFZ");
		String POS_CUSTOMER_HK_YHMC = parms.optString("POS_CUSTOMER_HK_YHMC");
		String POS_CUSTOMER_HK_CARD_NUM = parms.optString("POS_CUSTOMER_HK_CARD_NUM");
		String POS_CUSTOMER_LY = parms.optString("POS_CUSTOMER_LY");
		
		double POS_MONEY = parms.optDouble("POS_MONEY");
		sql.append(" INSERT INTO t_zhfw_pos_info (ID, POS_CODE, OPER_USER_ID, OPER_DATE  ");
		sql.append(" ,POS_OWNER_USER_ID, POS_OWNER_SJHM, POS_CONTENT, POS_STATE,POS_MONEY,POS_BIZ_GYH ");
		sql.append(" ,POS_PLATFORM, POS_CUSTOMER_SEX, POS_CUSTOMER_SFZ, POS_CUSTOMER_HK_YHMC  ");
		sql.append(" ,POS_CUSTOMER_HK_CARD_NUM, POS_CUSTOMER_LY	)");
		sql.append(" VALUES (uuid(),:POS_CODE,:OPER_USER_ID,now()  ");
		sql.append(" ,:POS_OWNER_USER_ID,:POS_OWNER_SJHM,:POS_CONTENT,'00',:POS_MONEY,:POS_BIZ_GYH ");
		sql.append(" ,:POS_PLATFORM, :POS_CUSTOMER_SEX, :POS_CUSTOMER_SFZ, :POS_CUSTOMER_HK_YHMC  ");
		sql.append(" ,:POS_CUSTOMER_HK_CARD_NUM, :POS_CUSTOMER_LY	)");
		Map<String, Object> parmsMap = new HashMap<String, Object>();
		parmsMap.put("POS_CODE", POS_CODE);
		parmsMap.put("OPER_USER_ID", OPER_USER_ID);
		parmsMap.put("POS_OWNER_USER_ID", POS_OWNER_USER_ID);
		parmsMap.put("POS_OWNER_SJHM", POS_OWNER_SJHM);
		parmsMap.put("POS_CONTENT", POS_CONTENT);
		parmsMap.put("POS_MONEY", POS_MONEY);
		parmsMap.put("POS_BIZ_GYH", POS_BIZ_GYH);
		
		parmsMap.put("POS_PLATFORM", POS_PLATFORM);
		parmsMap.put("POS_CUSTOMER_SEX", POS_CUSTOMER_SEX);
		parmsMap.put("POS_CUSTOMER_SFZ", POS_CUSTOMER_SFZ);
		parmsMap.put("POS_CUSTOMER_HK_YHMC", POS_CUSTOMER_HK_YHMC);
		parmsMap.put("POS_CUSTOMER_HK_CARD_NUM", POS_CUSTOMER_HK_CARD_NUM);
		parmsMap.put("POS_CUSTOMER_LY", POS_CUSTOMER_LY);
		return this.getNamedParameterJdbcTemplate().update(sql.toString(), parmsMap);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see com.bringway.p2p.dao.POSDao#searchPos(net.sf.json.JSONObject)
	 */
	@Override
	public List searchPos(JSONObject parms) {
		StringBuffer sql = new StringBuffer();
		Map<String, Object> parmsMap = new HashMap<String, Object>();
		sql.append( " SELECT p.POS_BIZ_GYH,p.ID,p.POS_CODE,p.OPER_USER_ID,DATE_FORMAT(p.OPER_DATE,'%Y-%m-%d %T') as OPER_DATE   ");
		sql.append("   ,p.POS_OWNER_USER_ID,p.POS_OWNER_SJHM,p.POS_CONTENT,p.POS_STATE,p.POS_MONEY ");
		sql.append("   ,p.POS_PLATFORM, p.POS_CUSTOMER_SEX, p.POS_CUSTOMER_SFZ, p.POS_CUSTOMER_HK_YHMC, p.POS_CUSTOMER_HK_CARD_NUM");
		sql.append("   ,p.POS_CUSTOMER_LY ");
		sql.append(" FROM t_zhfw_pos_info p ");
		sql.append(" where 1=1 ");
		String POS_CODE = parms.optString("POS_CODE");
		String OPER_USER_ID = parms.optString("OPER_USER_ID");
		String POS_OWNER_USER_ID = parms.optString("POS_OWNER_USER_ID");
		String POS_OWNER_SJHM = parms.optString("POS_OWNER_SJHM");
		String POS_STATE = parms.optString("POS_STATE");
		String OPER_DATE_START = parms.optString("OPER_DATE_START");
		String OPER_DATE_END = parms.optString("OPER_DATE_END");
		double POS_MONEY_START = parms.optDouble("POS_MONEY_START");
		double POS_MONEY_END = parms.optDouble("POS_MONEY_END");
		int start = parms.optInt("start");
		int limit = parms.optInt("limit");
		if (StringUtils.isNotEmpty(POS_CODE)) {
			sql.append(" and POS_CODE like :POS_CODE ");
			parmsMap.put("POS_CODE", "%"+POS_CODE+"%");
		}
		if (StringUtils.isNotEmpty(OPER_USER_ID)) {
			sql.append(" and OPER_USER_ID like :OPER_USER_ID ");
			parmsMap.put("OPER_USER_ID", "%"+OPER_USER_ID+"%");
		}
		if (StringUtils.isNotEmpty(POS_OWNER_USER_ID)) {
			sql.append(" and POS_OWNER_USER_ID like :POS_OWNER_USER_ID ");
			parmsMap.put("POS_OWNER_USER_ID", "%"+POS_OWNER_USER_ID+"%");
		}
		if (StringUtils.isNotEmpty(POS_OWNER_SJHM)) {
			sql.append(" and POS_OWNER_SJHM like :POS_OWNER_SJHM ");
			parmsMap.put("POS_OWNER_SJHM", "%" + POS_OWNER_SJHM + "%");
		}
		if (StringUtils.isNotEmpty(POS_STATE)) {
			sql.append(" and POS_STATE = :POS_STATE ");
			parmsMap.put("POS_STATE", "0"+POS_STATE);
		}
		if (StringUtils.isNotEmpty(OPER_DATE_START)) {
			sql.append(" and OPER_DATE >= :OPER_DATE_START ");
			parmsMap.put("OPER_DATE_START", OPER_DATE_START+" 00:00:00");
		}
		if (StringUtils.isNotEmpty(OPER_DATE_END)) {
			sql.append(" and OPER_DATE <= :OPER_DATE_END ");
			parmsMap.put("OPER_DATE_END", OPER_DATE_END+" 23:59:59");
		}
		if(POS_MONEY_START!=0.0){
			sql.append(" and POS_MONEY >= :POS_MONEY_START ");
			parmsMap.put("POS_MONEY_START", POS_MONEY_START);
		}
		if(POS_MONEY_END!=0.0){
			sql.append(" and POS_MONEY <= :POS_MONEY_END ");
			parmsMap.put("POS_MONEY_END", POS_MONEY_END);
		}
		sql.append(" order by p.OPER_DATE desc limit :start,:limit ");
		parmsMap.put("start", start);
		parmsMap.put("limit", limit);
		return this.getNamedParameterJdbcTemplate().queryForList(sql.toString(), parmsMap);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see com.bringway.p2p.dao.POSDao#getTotalItem(net.sf.json.JSONObject)
	 */
	@Override
	public int getTotalItem(JSONObject parms) {
		StringBuffer sql = new StringBuffer();
		Map<String, Object> parmsMap = new HashMap<String, Object>();
		sql.append(" SELECT count(*) FROM t_zhfw_pos_info p  ");
		sql.append(" where 1=1 ");
		String POS_CODE = parms.optString("POS_CODE");
		String OPER_USER_ID = parms.optString("OPER_USER_ID");
		String POS_OWNER_USER_ID = parms.optString("POS_OWNER_USER_ID");
		String POS_OWNER_SJHM = parms.optString("POS_OWNER_SJHM");
		String POS_STATE = parms.optString("POS_STATE");
		String OPER_DATE_START = parms.optString("OPER_DATE_START");
		String OPER_DATE_END = parms.optString("OPER_DATE_END");
		double POS_MONEY_START = parms.optDouble("POS_MONEY_START");
		double POS_MONEY_END = parms.optDouble("POS_MONEY_END");
		if (StringUtils.isNotEmpty(POS_CODE)) {
			sql.append(" and POS_CODE = :POS_CODE ");
			parmsMap.put("POS_CODE", POS_CODE);
		}
		if (StringUtils.isNotEmpty(OPER_USER_ID)) {
			sql.append(" and OPER_USER_ID = :OPER_USER_ID ");
			parmsMap.put("OPER_USER_ID", OPER_USER_ID);
		}
		if (StringUtils.isNotEmpty(POS_OWNER_USER_ID)) {
			sql.append(" and POS_OWNER_USER_ID = :POS_OWNER_USER_ID ");
			parmsMap.put("POS_OWNER_USER_ID", POS_OWNER_USER_ID);
		}
		if (StringUtils.isNotEmpty(POS_OWNER_SJHM)) {
			sql.append(" and POS_OWNER_SJHM like :POS_OWNER_SJHM ");
			parmsMap.put("POS_OWNER_SJHM", "%" + POS_OWNER_SJHM + "%");
		}
		if (StringUtils.isNotEmpty(POS_STATE)) {
			sql.append(" and POS_STATE = :POS_STATE ");
			parmsMap.put("POS_STATE", POS_STATE);
		}
		if (StringUtils.isNotEmpty(OPER_DATE_START)) {
			sql.append(" and OPER_DATE >= :OPER_DATE_START ");
			parmsMap.put("OPER_DATE_START", OPER_DATE_START+" 00:00:00");
		}
		if (StringUtils.isNotEmpty(OPER_DATE_END)) {
			sql.append(" and OPER_DATE <= :OPER_DATE_END ");
			parmsMap.put("OPER_DATE_END", OPER_DATE_END+" 23:59:59");
		}
		if(POS_MONEY_START!=0.0){
			sql.append(" and POS_MONEY >= :POS_MONEY_START ");
			parmsMap.put("POS_MONEY_START", POS_MONEY_START);
		}
		if(POS_MONEY_END!=0.0){
			sql.append(" and POS_MONEY <= :POS_MONEY_END ");
			parmsMap.put("POS_MONEY_END", POS_MONEY_END);
		}
		return this.getNamedParameterJdbcTemplate().queryForObject(sql.toString(), parmsMap, Integer.class);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * com.bringway.p2p.dao.POSDao#posCodeInfoSearch(net.sf.json.JSONObject)
	 */
	@Override
	public List posCodeInfoSearch(JSONObject parms) {
		StringBuffer sql = new StringBuffer();
		Map<String,Object> parmsMap = new HashMap<String,Object>();
		String POS_CODE = parms.optString("POS_CODE");
		String OPER_USER_ID = parms.optString("OPER_USER_ID");
		String POS_OWNER_USER_ID = parms.optString("POS_OWNER_USER_ID");
		String POS_OWNER_SJHM = parms.optString("POS_OWNER_SJHM");
		String SH_STATE = parms.optString("SH_STATE");
//		String OPER_DATE_START = parms.optString("OPER_DATE_START");
//		String OPER_DATE_END = parms.optString("OPER_DATE_END");
		String TZSJ_START = parms.optString("TZSJ_START");
		String TZSJ_END = parms.optString("TZSJ_END");
		int start = parms.optInt("start");
		int limit = parms.optInt("limit");
		sql.append(" SELECT " 
		+" t1.ID, " 
		+" t1.POS_CODE, " 
		+" t1.OPER_USER_ID, "
		+" t1.POS_BIZ_GYH, "
		+" DATE_FORMAT(t1.OPER_DATE, '%Y-%m-%d %T') AS OPER_DATE, " 
		+" t1.POS_OWNER_USER_ID, "
		+" t1.POS_OWNER_SJHM, " 
		+" t1.POS_CONTENT, " 
		+" t1.POS_STATE, "
		+" t1.POS_PLATFORM, "
		+" t1.POS_CUSTOMER_SEX,"
		+" t1.POS_CUSTOMER_SFZ,"
		+" t1.POS_CUSTOMER_HK_YHMC,"
		+" t1.POS_CUSTOMER_HK_CARD_NUM, "
		+" t1.POS_CUSTOMER_LY, "
		+" t2.ID as ID2, "
		+" t2.POS_ID, "
		+" t2.USER_ID, "
		+" t2.ZQ_ID, "
		+" t2.TZJE, "
		+" DATE_FORMAT(t2.TZSJ, '%Y-%m-%d %T') AS TZSJ,"
		+" t2.SH_STATE, "
		+" t2.SH_DATE, "
		+" t2.SH_CONTENT, "
		+" t2.ZB_SH_USER_ID, "
		+" t2.ZB_SH_DATE, "
		+" t2.ZB_SH_CONTENT "
		+" from t_zhfw_pos_info t1 "
		+" right join t_zhfw_pos_tzrxx t2 on t1.POS_CODE = t2.POS_CODE and t1.ID = t2.POS_ID where 1=1 ");
		if (StringUtils.isNotEmpty(POS_CODE)) {
			sql.append(" and t1.POS_CODE like :POS_CODE ");
			parmsMap.put("POS_CODE", "%"+POS_CODE+"%");
		}
		if (StringUtils.isNotEmpty(OPER_USER_ID)) {
			sql.append(" and t1.OPER_USER_ID like :OPER_USER_ID ");
			parmsMap.put("OPER_USER_ID", "%"+OPER_USER_ID+"%");
		}
		if (StringUtils.isNotEmpty(POS_OWNER_USER_ID)) {
			sql.append(" and t1.POS_OWNER_USER_ID like :POS_OWNER_USER_ID ");
			parmsMap.put("POS_OWNER_USER_ID", "%"+POS_OWNER_USER_ID+"%");
		}
		if (StringUtils.isNotEmpty(POS_OWNER_SJHM)) {
			sql.append(" and t1.POS_OWNER_SJHM like :POS_OWNER_SJHM ");
			parmsMap.put("POS_OWNER_SJHM", "%" + POS_OWNER_SJHM + "%");
		}
		if (StringUtils.isNotEmpty(SH_STATE)) {
			sql.append(" and t2.SH_STATE = :SH_STATE ");
			parmsMap.put("SH_STATE", SH_STATE);
		}
		if (StringUtils.isNotEmpty(TZSJ_START)) {
			sql.append(" and t2.TZSJ >= :TZSJ_START ");
			parmsMap.put("TZSJ_START", TZSJ_START+" 00:00:00");
		}
		if (StringUtils.isNotEmpty(TZSJ_END)) {
			sql.append(" and t2.TZSJ <= :TZSJ_END ");
			parmsMap.put("TZSJ_END", TZSJ_END+ " 23:59:59");
		}
		sql.append(" order by t2.TZSJ desc limit :START,:LIMIT ");
		parmsMap.put("START", start);
		parmsMap.put("LIMIT", limit);
		logger.info("posCodeInfoSearch sql is : "+ sql.toString());
		logger.info("posCodeInfoSearch parmsMap is : "+ parmsMap.toString());
		return this.getNamedParameterJdbcTemplate().queryForList(sql.toString(), parmsMap);
	}
	
	/*
	 * (non-Javadoc)
	 * @see com.bringway.p2p.dao.POSDao#posCodeInfoSearchItems(net.sf.json.JSONObject)
	 */
	@Override
	public int posCodeInfoSearchItems(JSONObject parms) {
		StringBuffer sql = new StringBuffer();
		Map<String,Object> parmsMap = new HashMap<String,Object>();
		String POS_CODE = parms.optString("POS_CODE");
		String OPER_USER_ID = parms.optString("OPER_USER_ID");
		String POS_OWNER_USER_ID = parms.optString("POS_OWNER_USER_ID");
		String POS_OWNER_SJHM = parms.optString("POS_OWNER_SJHM");
		String SH_STATE = parms.optString("SH_STATE");
//		String OPER_DATE_START = parms.optString("OPER_DATE_START");
//		String OPER_DATE_END = parms.optString("OPER_DATE_END");
		String TZSJ_START = parms.optString("TZSJ_START");
		String TZSJ_END = parms.optString("TZSJ_END");
		sql.append(" SELECT count(*) "
		+" from t_zhfw_pos_info t1 "
		+" right join t_zhfw_pos_tzrxx t2 on t1.POS_CODE = t2.POS_CODE and t1.ID = t2.POS_ID ");
		if (StringUtils.isNotEmpty(POS_CODE)) {
			sql.append(" and t1.POS_CODE = :POS_CODE ");
			parmsMap.put("POS_CODE", POS_CODE);
		}
		if (StringUtils.isNotEmpty(OPER_USER_ID)) {
			sql.append(" and t1.OPER_USER_ID = :OPER_USER_ID ");
			parmsMap.put("OPER_USER_ID", OPER_USER_ID);
		}
		if (StringUtils.isNotEmpty(POS_OWNER_USER_ID)) {
			sql.append(" and t1.POS_OWNER_USER_ID = :POS_OWNER_USER_ID ");
			parmsMap.put("POS_OWNER_USER_ID", POS_OWNER_USER_ID);
		}
		if (StringUtils.isNotEmpty(POS_OWNER_SJHM)) {
			sql.append(" and t1.POS_OWNER_SJHM like :POS_OWNER_SJHM ");
			parmsMap.put("POS_OWNER_SJHM", "%" + POS_OWNER_SJHM + "%");
		}
		if (StringUtils.isNotEmpty(SH_STATE)) {
			sql.append(" and t2.SH_STATE = :SH_STATE ");
			parmsMap.put("SH_STATE", SH_STATE);
		}
		if (StringUtils.isNotEmpty(TZSJ_START)) {
			sql.append(" and t2.TZSJ >= :TZSJ_START ");
			parmsMap.put("TZSJ_START", TZSJ_START+" 00:00:00");
		}
		if (StringUtils.isNotEmpty(TZSJ_END)) {
			sql.append(" and t2.TZSJ <= :TZSJ_END ");
			parmsMap.put("TZSJ_END", TZSJ_END+" 23:59:59");
		}
		return this.getNamedParameterJdbcTemplate().queryForObject(sql.toString(), parmsMap, Integer.class);
	}

	@Override
	public boolean posShenHe1(JSONObject parms) {
		String userId = parms.optString("userId");
        String SH_STATE = parms.optString("SH_STATE");
		String SH_CONTENT = parms.optString("SH_CONTENT");
		String ID = parms.optString("ID");
		StringBuffer sql = new StringBuffer();
		sql.append(" UPDATE t_zhfw_pos_tzrxx SET SH_USER_ID = :SH_USER_ID, SH_DATE = NOW() ,SH_CONTENT = :SH_CONTENT, SH_STATE = :SH_STATE WHERE ID = :ID  ");
		Map<String,Object> parmsMap = new HashMap<String,Object>();
		parmsMap.put("SH_USER_ID", userId);
		parmsMap.put("SH_STATE",SH_STATE);
		parmsMap.put("SH_CONTENT",SH_CONTENT);
		parmsMap.put("ID",ID);
		logger.info("posShenHe1 sql is " + sql.toString());
		logger.info("posShenHe1 parmsMap is " + parmsMap);
		
		int i = this.getNamedParameterJdbcTemplate().update(sql.toString(), parmsMap);
//		String POS_ID  = parms.optString("POS_ID");
//		String POS_STATE = parms.optString("SH_STATE");
//		sql.setLength(0);
//		sql.append(" update t_zhfw_pos_info t set t.POS_STATE = :POS_STATE where t.ID = :ID ");
//		parmsMap.clear();
//		parmsMap.put("ID", POS_ID);
//		parmsMap.put("POS_STATE",POS_STATE);
		int j = this.getNamedParameterJdbcTemplate().update(sql.toString(),parmsMap);
		if(i>0&&j>0){
			return true;
		}else{
			return false;
		}
	}

	@Override
	public List<HashMap<String,String>> queryPosCodeHkList(JSONObject params) {
		int start = params.optInt("start");
		int limit = params.optInt("limit");
		String ZQHK_START = params.optString("ZQHK_START");
		String ZQHK_END = params.optString("ZQHK_END");
		String Hk_STATE = params.optString("Hk_STATE");
		String ZQ_ID = params.optString("ZQ_ID");
		String POS_PLATFORM_STATE = params.optString("POS_PLATFORM_STATE");
		String POS_CODE = params.optString("POS_CODE");
		String queryType = params.optString("queryType");
		StringBuffer sql = new StringBuffer();
		Map<String, Object> paramMap = new HashMap<>();
		sql.append(" SELECT tt1.ID,tt1.USERID,tt1.ZQID,tt1.TCJE,tt1.HKZT,tt1.TZSJ,tt1.POS_CODE,tt1.TZ_SOURCE,temp.DETAILS_NHL                    	");
		sql.append(" 	,tt2.SJSYL,tt2.SJSYL_SHOW, tt2.QX, tt2.JXRQ,date_add(tt2.JXRQ,INTERVAL tt2.QX DAY) HKRQ             		");
		sql.append("	,tt3.OPER_USER_ID , tt3.POS_OWNER_USER_ID,tt3.POS_OWNER_SJHM,tt3.POS_BIZ_GYH												");
		sql.append(" 	,tt3.POS_PLATFORM, tt3.POS_CUSTOMER_SEX, tt3.POS_CUSTOMER_SFZ, tt3.POS_CUSTOMER_HK_YHMC  	");
		sql.append(" 	,tt3.POS_CUSTOMER_HK_CARD_NUM, tt3.POS_CUSTOMER_LY											");
		sql.append(" 	,tt4.SH_USER_ID, tt4.SH_DATE, tt4.SH_CONTENT 																				");
		sql.append("    ,CASE WHEN  temp.DETAILS_NHL IS NOT NULL THEN                                                                                  ");
		sql.append(" 		ROUND((tt1.TCJE * temp.DETAILS_NHL * tt2.QX )/100/365,2)                                                                ");
		sql.append(" 	 ELSE                                                                                                                        ");
		sql.append(" 		ROUND((tt1.TCJE * tt2.SJSYL * tt2.QX )/100/365 ,2)                                                                      ");
		sql.append(" 	 END YS_TZSY                                                                                                                 ");
		sql.append("	,tt5.HK_USER_ID, date_format(tt5.HK_DATE,'%Y-%m-%d %H:%i:%s') HK_DATE, tt5.HK_CONTENT, tt5.HK_STATE ");
		sql.append(" 	FROM t_zhfw_tzrxx tt1                                                                                                       ");
		sql.append(" 	left join (                                                                                                                 ");
		sql.append(" 		select t1.ID,t1.ZQFD_ID,t1.ZQ_ID,t2.FD_NAME,t2.FD_SEQ, t2.FD_CONTENT ,t3.ZQFD_DETAILS_ID ,                              ");
		sql.append(" 		t4.DETAILS_NAME,t4.DETAILS_NHL,t4.DETAILS_MIN_JE,t4.DETAILS_MAX_JE,t4.DETAILS_CONTENT,t4.CREATE_USER_ID,t4.CREATE_DATE  ");
		sql.append(" 		from t_zhfw_zqfd_use t1                                                                                                 ");
		sql.append(" 		left join t_zhfw_zqfd t2 on t1.ZQFD_ID = t2.ID                                                                          ");
		sql.append(" 		left join t_zhfw_zqfd_details_use t3 on t1.ZQFD_ID = t3.ZQFD_ID                                                         ");
		sql.append(" 		left join t_zhfw_zqfd_details t4 on t3.ZQFD_DETAILS_ID = t4.ID                                                          ");
		sql.append(" 		)temp on tt1.ZQID = temp.ZQ_ID AND convert( tt1.TCJE , decimal(11,2)) >= convert( temp.DETAILS_MIN_JE , decimal(11,2))  ");
		sql.append(" 		AND convert( tt1.TCJE , decimal(11,2)) < convert( temp.DETAILS_MAX_JE , decimal(11,2))                                  ");
		sql.append(" 	left join t_zhfw_zqzrlb tt2 on tt1.ZQID = tt2.ID                                                                            ");
		sql.append("	left join t_zhfw_pos_info tt3 on tt1.POS_CODE = tt3.POS_CODE																");
		sql.append("	left join t_zhfw_pos_tzrxx tt4 on tt1.POS_CODE = tt4.POS_CODE 																");
		sql.append("	left join t_zhfw_pos_hk tt5 on tt1.ID = tt5.TZRXX_ID 				");
		sql.append("      where tt1.tz_source = '01'                                                                                         		");
		if(StringUtils.isNotEmpty(Hk_STATE)){//00：未处理，01：还款完成，02：还款失败
			if("00".equals(Hk_STATE)){//可还款，当前日期 大于等于 还款日期
				sql.append(" and tt5.HK_STATE IS NULL AND NOW() >= date_add(tt2.JXRQ,INTERVAL tt2.QX DAY) ");
			}if("001".equals(Hk_STATE)){//待还款,未执行还款操作
				sql.append(" and tt5.HK_STATE IS NULL ");
			}else if("01".equals(Hk_STATE)){//还款完成
				sql.append(" and tt5.HK_STATE = '01' ");
			}else if("02".equals(Hk_STATE)){//还款失败
				sql.append(" and tt5.HK_STATE = '02' ");
			}
		}
		if(StringUtils.isNotEmpty(ZQHK_START)){
			sql.append(" and date_add(tt2.JXRQ,INTERVAL tt2.QX DAY) >= :ZQHK_START ");
			paramMap.put("ZQHK_START", ZQHK_START);
		}
		if(StringUtils.isNotEmpty(ZQHK_END)){
			sql.append(" and date_add(tt2.JXRQ,INTERVAL tt2.QX DAY) <= :ZQHK_END ");
			paramMap.put("ZQHK_END", ZQHK_END);
		}
		if(StringUtils.isNotBlank(ZQ_ID)){
			sql.append(" and tt1.ZQID = :ZQID ");
			paramMap.put("ZQID", ZQ_ID);
		}
		if(StringUtils.isNotEmpty(POS_PLATFORM_STATE)){
			sql.append(" and tt3.POS_PLATFORM = :POS_PLATFORM_STATE ");
			paramMap.put("POS_PLATFORM_STATE", POS_PLATFORM_STATE);
		}
		if(StringUtils.isNotEmpty(POS_CODE)){
			sql.append(" and tt1.POS_CODE LIKE :POS_CODE ");
			paramMap.put("POS_CODE", "%" + POS_CODE + "%");
		}
		sql.append(" order by hk_state asc,date_add(tt2.JXRQ,INTERVAL tt2.QX DAY) desc ");
		if(0 != limit && !"exportExcel".equals(queryType)){
			sql.append(" LIMIT :START,:LIMIT ");
			paramMap.put("START", start);
			paramMap.put("LIMIT", limit);
		}
		logger.info("queryPosCodeHkList sql is : " + sql.toString());
		logger.info("queryPosCodeHkList paramMap is : " + paramMap.toString());
		return this.getNamedParameterJdbcTemplate().query(sql.toString(), paramMap, new ResultSetExtractor<List<HashMap<String,String>>>() {
			@Override
			public List<HashMap<String,String>> extractData(ResultSet rs) throws SQLException, DataAccessException {
				List<HashMap<String,String>> list = new ArrayList<>();
        		ResultSetMetaData rmeta = null;
        		try {
        			rmeta = rs.getMetaData();
        			int numColumns = rmeta.getColumnCount();
        			while(rs.next()){
        				HashMap<String,String> hm = new HashMap<String,String>();
        				for(int i = 0;i< numColumns;i++){
        					if(hm.containsKey(rmeta.getColumnName(i+1))){
        						hm.remove(rmeta.getColumnName(i+1));
        					}
        					hm.put(rmeta.getColumnName(i+1), rs.getString(i+1));
        				}
        				String tzje =  hm.get("TCJE");
        				String ysTzsy = hm.get("YS_TZSY");
        				double tzjeD = 0.0;
        				double ysTzsyD = 0.0;
        				if(StringUtils.isNotEmpty(tzje)){
        					tzjeD = Double.parseDouble(tzje);
        				}
        				if(StringUtils.isNotEmpty(ysTzsy)){
        					ysTzsyD = Double.parseDouble(ysTzsy);
        				}
        				double yjHkje = tzjeD + ysTzsyD;
        				DecimalFormat df = new DecimalFormat("#.##");
        				hm.put("YJ_HKJE", String.valueOf(df.format(yjHkje)));
        				list.add(hm);
        			}
        		} catch (SQLException e) {
        			e.printStackTrace();
        		}
        		return list;
			}
		});
	}

	@Override
	public int getPosCodeHkCount(JSONObject params) {
		String ZQHK_START = params.optString("ZQHK_START");
		String ZQHK_END = params.optString("ZQHK_END");
		String Hk_STATE = params.optString("Hk_STATE");
		String ZQ_ID = params.optString("ZQ_ID");
		String POS_PLATFORM_STATE = params.optString("POS_PLATFORM_STATE");
		String POS_CODE = params.optString("POS_CODE");
		StringBuffer sql = new StringBuffer();
		Map<String, String> paramMap = new HashMap<>();
		sql.append(" SELECT COUNT(*) CT  ");
		sql.append(" FROM t_zhfw_tzrxx tt1   ");
		sql.append(" 	left join t_zhfw_zqzrlb tt2 on tt1.ZQID = tt2.ID     ");
		sql.append("	left join t_zhfw_pos_info tt3 on tt1.POS_CODE = tt3.POS_CODE ");
		sql.append("	left join t_zhfw_pos_hk tt5 on tt1.ID = tt5.TZRXX_ID ");
		sql.append(" WHERE tt1.tz_source = '01' ");
		if(StringUtils.isNotEmpty(Hk_STATE)){//00：未处理，01：还款完成，02：还款失败
			if("00".equals(Hk_STATE)){//可还款，当前日期 大于等于 还款日期
				sql.append(" and tt5.HK_STATE IS NULL AND NOW() >= date_add(tt2.JXRQ,INTERVAL tt2.QX DAY) ");
			}if("001".equals(Hk_STATE)){//待还款,未执行还款操作
				sql.append(" and tt5.HK_STATE IS NULL ");
			}else if("01".equals(Hk_STATE)){//还款完成
				sql.append(" and tt5.HK_STATE = '01' ");
			}else if("02".equals(Hk_STATE)){//还款失败
				sql.append(" and tt5.HK_STATE = '02' ");
			}
		}
		if(StringUtils.isNotEmpty(ZQHK_START)){
			sql.append(" and date_add(tt2.JXRQ,INTERVAL tt2.QX DAY) >= :ZQHK_START ");
			paramMap.put("ZQHK_START", ZQHK_START);
		}
		if(StringUtils.isNotEmpty(ZQHK_END)){
			sql.append(" and date_add(tt2.JXRQ,INTERVAL tt2.QX DAY) <= :ZQHK_END ");
			paramMap.put("ZQHK_END", ZQHK_END);
		}
		if(StringUtils.isNotBlank(ZQ_ID)){
			sql.append(" and tt1.ZQID = :ZQID ");
			paramMap.put("ZQID", ZQ_ID);
		}
		if(StringUtils.isNotEmpty(POS_PLATFORM_STATE)){
			sql.append(" and tt3.POS_PLATFORM = :POS_PLATFORM_STATE ");
			paramMap.put("POS_PLATFORM_STATE", POS_PLATFORM_STATE);
		}
		if(StringUtils.isNotEmpty(POS_CODE)){
			sql.append(" and tt1.POS_CODE LIKE :POS_CODE ");
			paramMap.put("POS_CODE", "%" + POS_CODE + "%");
		}
		logger.info("getPosCodeHkCount sql is " + sql.toString());
		logger.info("getPosCodeHkCount paramMap is " + paramMap.toString());
		return this.getNamedParameterJdbcTemplate().queryForObject(sql.toString(), paramMap, Integer.class);
	}

	@Override
	public void savePosHk(JSONObject params) {
		String tzrxxId = params.optString("tzrxxId");
		String posCode = params.optString("posCode");
		String tzje = params.optString("tzje");
		String tzsyje = params.optString("tzsyje");
		String hkContent = params.optString("hkContent");
		String hkUserId = params.optString("hkUserId");
		Double tztxje = 0.0;
		Double tzjeD = Double.parseDouble(tzje);
		Double tzsyjeD = Double.parseDouble(tzsyje);
		tztxje = tzjeD + tzsyjeD;
		StringBuffer sql = new StringBuffer();
		sql.append(" INSERT INTO t_zhfw_pos_hk(ID, TZRXX_ID, POS_CODE, HK_USER_ID, HK_DATE, HK_TZ_JE, HK_SY_JE, HK_TX_JE, HK_CONTENT,HK_STATE) ");
		sql.append(" VALUES(UUID(), :TZRXX_ID, :POS_CODE, :HK_USER_ID, NOW(), :HK_TZ_JE, :HK_SY_JE, :HK_TX_JE, :HK_CONTENT, '01') ");
		Map<String,String> paramMap = new HashMap<>();
		paramMap.put("TZRXX_ID", tzrxxId);
		paramMap.put("POS_CODE", posCode);
		paramMap.put("HK_USER_ID", hkUserId);
		paramMap.put("HK_TZ_JE", tzje);
		paramMap.put("HK_SY_JE", tzsyje);
		paramMap.put("HK_TX_JE", String.valueOf(tztxje));
		paramMap.put("HK_CONTENT", hkContent);
		logger.info("savePosHk sql is " + sql.toString());
		logger.info("savePosHk paramMap is " + paramMap.toString());
		this.getNamedParameterJdbcTemplate().update(sql.toString(), paramMap);
	}

	@SuppressWarnings("unchecked")
	@Override
	public void updateUserZcxx(JSONObject params) {
		String tzrxxId = params.optString("tzrxxId");
		String tzje = params.optString("tzje");
		String tzsyje = params.optString("tzsyje");
		StringBuffer sql1 = new StringBuffer();//获得投资人信息userid
		StringBuffer sql2 = new StringBuffer();//更新投资信息还款信息
		StringBuffer sql3 = new StringBuffer();//更新账户余额信息
		StringBuffer sql4 = new StringBuffer();//
		Map<String,String> paramMap = new HashMap<String,String>();
		sql1.append(" SELECT USERID FROM T_ZHFW_TZRXX WHERE ID = '"+tzrxxId+"'");//获得客户账户
		String userId = "";
		if(this.getNamedParameterJdbcTemplate().queryForList(sql1.toString(), paramMap).size() > 0){
			logger.info("updateUserZcxx sql1 is " + sql1.toString());
			userId = this.getNamedParameterJdbcTemplate().queryForObject(sql1.toString(), paramMap, String.class);
		}
		if(StringUtils.isNotEmpty(userId)){
			sql2.append(" UPDATE T_ZHFW_TZRXX SET jxrq = (DATE_FORMAT(NOW(),'%Y-%m-%d')),TZSY = '"+tzsyje+"',hkzt = '3' where userid = '"+userId+"' and id ='"+tzrxxId+"' ");
			logger.info("updateUserZcxx sql2 is " + sql2.toString());
			//更新客户投资信息，设置还款金额，及时间
			this.getNamedParameterJdbcTemplate().update(sql2.toString(), paramMap);
			String sqlJbxx = " SELECT DJJE,KYJE,ZHYE FROM T_ZHFW_JBXX WHERE gyh = '"+userId+"'";
			logger.info("updateUserZcxx sqlJbxx is " + sqlJbxx.toString());
			JSONObject userJbxxJson = this.getNamedParameterJdbcTemplate().query(sqlJbxx.toLowerCase(), new ResultSetExtractor<JSONObject>(){
				@Override
				public JSONObject extractData(ResultSet rs) throws SQLException, DataAccessException {
					JSONObject ujson = new JSONObject();
					if(rs.next()){
						ujson.put("DJJE", rs.getDouble("DJJE"));
						ujson.put("KYJE", rs.getDouble("KYJE"));
						ujson.put("ZHYE", rs.getDouble("ZHYE"));
					}
					return ujson;
				}
				
			});
			Double DJJE = userJbxxJson.optDouble("DJJE");
			Double KYJE = userJbxxJson.optDouble("KYJE");
			Double ZHYE = userJbxxJson.optDouble("ZHYE");
			
			//更新客户账户信息，减少冻结金额，账户余额
			sql3.append(" UPDATE T_ZHFW_JBXX set DJJE = convert(DJJE - '"+tzje+"',decimal(11,2)), ZHYE = convert(ZHYE - '"+tzje+"',decimal(11,2)) where GYH =  '"+userId+"'");
			logger.info("updateUserZcxx sql3 is " + sql3.toString());
			this.getNamedParameterJdbcTemplate().update(sql3.toString(), paramMap);
			//注入资产变动表，新增类型：21：pos单号投资还款成功
			/**
			 * 变动金额：提现打款金额
			 * 发生金额：pos单投标金额
			 * 冻结金额  = 冻结金额  - pos单投资金额
			 * 可用金额不变
			 * 账户余额  = 账户余额 - pos单投资金额
			 * */
			Double tztxje = 0.0;
			Double tzjeD = Double.parseDouble(tzje);
			Double tzsyjeD = Double.parseDouble(tzsyje);
			tztxje = tzjeD + tzsyjeD;
			sql4.append(" INSERT INTO t_zhfw_zc_details(USERNAME,ZC_CHANGE_TYPE,ZC_CHANGE_JE,ZC_FINAL_ZHYE	");
			sql4.append("	,ZC_CHANGE_ID,ZC_OCCUR_JE,ZC_ORIGINAL_DJJE,ZC_ORIGINAL_KYJE,ZC_ORIGINAL_ZHYE ");
			sql4.append("   ,ZC_FINAL_DJJE,ZC_FINAL_KYJE) ");
			sql4.append(" VALUES ('"+userId+"','21', "+tztxje+", '"+ZHYE+"' - '"+tzje+"' ");
			sql4.append("       ,'"+tzrxxId+"','"+tzje+"', '"+DJJE+"' ,'"+KYJE+"' ,'"+ZHYE+"' ");
			sql4.append("     ,'"+DJJE+"' - '"+tzje+"','"+KYJE+"'); ");
			logger.info("updateUserZcxx sql4 is " + sql4.toString());
			this.getNamedParameterJdbcTemplate().update(sql4.toString(), paramMap);
		}
		
		
	}

	@Override
	public boolean isHasTzrxxByPoscode(JSONObject parms) {
		boolean result = false;
		String posCode = parms.optString("posCode");
		if(StringUtils.isNotEmpty(posCode)){
			StringBuffer sql = new StringBuffer();
			sql.append(" select count(*) ct from t_zhfw_tzrxx where pos_code = :pos_code ");
			Map<String,String> paramMap = new HashMap<>();
			paramMap.put("pos_code", posCode);
			logger.info("isHasTzrxxByPoscode sql is " + sql.toString());
			logger.info("isHasTzrxxByPoscode paramMap is " + paramMap.toString());
			int count = this.getNamedParameterJdbcTemplate().queryForObject(sql.toString(), paramMap, Integer.class);
			if(count > 0){
				result = true;
			}else{
				result = false;
			}
		}
		return result;
	}

	@Override
	public boolean isReadyHk(JSONObject params) {
		String tzrxxId = params.optString("tzrxxId");
		StringBuffer sql = new StringBuffer();
		sql.append(" select count(*) ct from t_zhfw_tzrxx tt1 ");
		sql.append("	left join t_zhfw_zqzrlb tt2 on tt1.ZQID = tt2.ID ");
		sql.append(" WHERE NOW() >= date_add(tt2.JXRQ,INTERVAL tt2.QX DAY) ");
		sql.append(" AND   tt1.ID = :TZRXXID ");
		Map<String,String> paramMap = new HashMap<>();
		paramMap.put("TZRXXID", tzrxxId);
		logger.info("isReadyHk sql is " + sql.toString());
		logger.info("isReadyHk paramMap is " + paramMap.toString());
		int count = this.getNamedParameterJdbcTemplate().queryForObject(sql.toString(), paramMap, Integer.class);
		if(count > 0){//如果当前日期 大于 还款日期，则可以还款
			return true;
		}else{
			return false;
		}
	}
	
	
	@Override
	public void deletePos(JSONObject parms) {
		String bizId = parms.optString("bizId");
		StringBuffer sql = new StringBuffer();
		sql.append(" delete from t_zhfw_pos_info where id = :ID and pos_state = '00' ");
		SqlParameterSource parameters = new MapSqlParameterSource()
				.addValue("ID", bizId);
		logger.info("deletePos sql is " + sql.toString());
		logger.info("deletePos paramMap is " + parameters.toString());
		this.getNamedParameterJdbcTemplate().update(sql.toString(), parameters);
	}

	@Override
	public Map<String, Object> getPosInfo(JSONObject params) {
		String bizId = params.optString("bizId");
		StringBuffer sql = new StringBuffer();
		sql.append(" select * from t_zhfw_pos_info where id = :ID  ");
		SqlParameterSource parameters = new MapSqlParameterSource()
				.addValue("ID", bizId);
		logger.info("getPosInfo sql is " + sql.toString());
		logger.info("getPosInfo paramMap is " + parameters.toString());
		return this.getNamedParameterJdbcTemplate().queryForMap(sql.toString(), parameters);
	}

	@Override
	public boolean isHasHkByPoscode(JSONObject params) {
		String tzrxxId = params.optString("tzrxxId");
		String posCode = params.optString("posCode");
		StringBuffer sql = new StringBuffer();
		Map<String,String> paramMap = new HashMap<>();
		sql.append("  select count(*) ct from t_zhfw_pos_hk where pos_code = :POS_CODE and tzrxx_id = :TZRXX_ID ");
		paramMap.put("POS_CODE", posCode);
		paramMap.put("TZRXX_ID", tzrxxId);
		logger.info("isHasHkByPoscode sql is " + sql.toString());
		logger.info("isHasHkByPoscode paramMap is " + paramMap.toString());
		int ct = this.getNamedParameterJdbcTemplate().queryForObject(sql.toString(), paramMap, Integer.class);
		if(ct > 0){
			return true;
		}else{
			return false;
		}
	}


	@Override
	public int posZbShenHe(JSONObject params) {
		String userId = params.optString("userId");
        String SH_STATE = params.optString("SH_STATE");
		String ZB_SH_CONTENT = params.optString("ZB_SH_CONTENT");
		String ID = params.optString("ID");
		StringBuffer sql = new StringBuffer();
		sql.append(" UPDATE t_zhfw_pos_tzrxx SET ZB_SH_USER_ID = :ZB_SH_USER_ID, ZB_SH_DATE = NOW() ,ZB_SH_CONTENT = :ZB_SH_CONTENT, SH_STATE = :SH_STATE WHERE ID = :ID  ");
		Map<String,Object> parmsMap = new HashMap<String,Object>();
		parmsMap.put("ZB_SH_USER_ID", userId);
		parmsMap.put("SH_STATE",SH_STATE);
		parmsMap.put("ZB_SH_CONTENT",ZB_SH_CONTENT);
		parmsMap.put("ID",ID);
		return this.getNamedParameterJdbcTemplate().update(sql.toString(), parmsMap);
	}

	@Override
	public boolean isRealCustomer(JSONObject parms) {
		String POS_OWNER_USER_ID = parms.optString("POS_OWNER_USER_ID");//客户姓名
		String POS_CUSTOMER_SFZ = parms.optString("POS_CUSTOMER_SFZ");//客户身份证号码
		String POS_OWNER_SJHM = parms.optString("POS_OWNER_SJHM");//客户手机号码
		StringBuffer sql = new StringBuffer();
		Map<String,String> paramMap = new HashMap<>();
		sql.append(" SELECT COUNT(*) CT FROM T_ZHFW_SMRZ t1, t_zhfw_user t2 WHERE t1.user_name = t2.user_name and t2.DISABLED = '1' ");
		sql.append(" AND t1.real_name = :REAL_NAME and t1.sfz_num = :SFZ_NUM and t2.sjhm = :SJHM ");
		paramMap.put("REAL_NAME", POS_OWNER_USER_ID);
		paramMap.put("SFZ_NUM", POS_CUSTOMER_SFZ);
		paramMap.put("SJHM", POS_OWNER_SJHM);
		logger.info("isRealCustomer sql is " + sql.toString());
		logger.info("isRealCustomer paramMap is " + paramMap.toString());
		int ct = this.getNamedParameterJdbcTemplate().queryForObject(sql.toString(), paramMap, Integer.class);
		if(ct > 0){
			return true;
		}else{
			return false;
		}
	}

	@Override
	public boolean ishasPoscodeInfo(JSONObject parms) {
		String POS_CODE = parms.optString("POS_CODE");
		StringBuffer sql = new StringBuffer();
		Map<String,String> paramMap = new HashMap<>();
		sql.append(" SELECT COUNT(*) CT FROM t_zhfw_pos_info ");
		sql.append(" WHERE POS_CODE = :POS_CODE ");
		paramMap.put("POS_CODE", POS_CODE);
		logger.info("ishasPoscodeInfo sql is " + sql.toString());
		logger.info("ishasPoscodeInfo paramMap is " + paramMap.toString());
		int ct = this.getNamedParameterJdbcTemplate().queryForObject(sql.toString(), paramMap, Integer.class);
		if(ct > 0){
			return true;
		}else{
			return false;
		}
	}

	@Override
	public List<Map<String,Object>> queryCzPoscodeTzrxxList(JSONObject params) {
		int start = params.optInt("start");
		int limit = params.optInt("limit");
		String POS_CODE = params.optString("POS_CODE");
		String POS_OWNER_USER_ID = params.optString("POS_OWNER_USER_ID");
		String POS_OWNER_SJHM = params.optString("POS_OWNER_SJHM");
		String CX_STATE = params.optString("CX_STATE");
		String currentUserId = params.optString("currentUserId");
		StringBuffer sql = new StringBuffer();
		Map<String,Object> paramMap = new HashMap<>();
		sql.append(" select t1.ID, t1.USERID, t1.ZQID, t1.TCJE, date_format(t1.TZSJ,'%Y-%m-%d %H:%i:%s') TZSJ, t1.TCZT, t1.HKZT, t1.JXRQ HKRQ, t1.TZSY, t1.TZ_SOURCE ");
		sql.append(" 	 ,t1.POS_CODE, t2.POS_MONEY, t2.POS_BIZ_GYH, t2.POS_OWNER_USER_ID, t2.POS_OWNER_SJHM, t2.POS_PLATFORM, t2.POS_CUSTOMER_SEX  ");
		sql.append("     ,t2.POS_CUSTOMER_SFZ, t2.POS_CUSTOMER_HK_YHMC, t2.POS_CUSTOMER_HK_CARD_NUM, t2.POS_CUSTOMER_LY        ");
		sql.append("     ,t2.OPER_USER_ID, date_format(t2.OPER_DATE,'%Y-%m-%d %H:%i:%s') OPER_DATE, t2.POS_CONTENT             ");
		sql.append("     ,t3.SH_USER_ID, date_format(t3.SH_DATE,'%Y-%m-%d %H:%i:%s') SH_DATE, t3.SH_CONTENT, t3.SH_STATE POS_STATE ");
		sql.append("     ,t4.XMMC, t4.SJSYL, t4.SJSYL_SHOW, t4.JE, t4.QX, t4.JD, t4.FBSJ, t4.ZT, t4.MJQ, t4.JXRQ               ");
		sql.append("	 ,t5.FB_SQ_USER_ID, date_format(t5.FB_SQ_DATE,'%Y-%m-%d %H:%i:%s') FB_SQ_DATE, t5.FB_SQ_CONTENT ");
		sql.append("	 ,t5.ZB_SH_USER_ID, date_format(t5.ZB_SH_DATE,'%Y-%m-%d %H:%i:%s') ZB_SH_DATE, t5.ZB_SH_CONTENT, t5.CX_STATE");
		sql.append("  from t_zhfw_tzrxx t1 ");
		sql.append(" left join t_zhfw_pos_info t2 on t1.POS_CODE = t2.POS_CODE ");
		sql.append(" left join t_zhfw_pos_tzrxx t3 on t1.POS_CODE = t3.POS_CODE ");
		sql.append(" left join t_zhfw_zqzrlb t4 on t1.ZQID = t4.ID ");
		sql.append(" left join t_zhfw_pos_cx_tzrxx t5 on t1.POS_CODE = t5.POS_CODE ");
		sql.append(" where t1.TZ_SOURCE = '01' and t1.TZSY is null ");
		sql.append(" 	and now() <= t4.JXRQ ");
		sql.append(" 	and t2.OPER_USER_ID = :OPER_USER_ID ");
		paramMap.put("OPER_USER_ID", currentUserId);
		if(StringUtils.isNotEmpty(POS_CODE)){
			sql.append(" AND t1.POS_CODE = :POS_CODE ");
			paramMap.put("POS_CODE", POS_CODE);
		}
		if(StringUtils.isNotEmpty(POS_OWNER_USER_ID)){
			sql.append(" AND t2.POS_OWNER_USER_ID = :POS_OWNER_USER_ID ");
			paramMap.put("POS_OWNER_USER_ID", POS_OWNER_USER_ID);
		}
		if(StringUtils.isNotEmpty(POS_OWNER_SJHM)){
			sql.append(" AND t2.POS_OWNER_SJHM LIKE :POS_OWNER_SJHM ");
			paramMap.put("POS_OWNER_SJHM", "%"+POS_OWNER_SJHM+"%");
		}
		if(StringUtils.isNotEmpty(CX_STATE)){
			if("0000".equals(CX_STATE)){//可撤销
				sql.append(" AND t5.CX_STATE IS NULL ");
			}else{
				sql.append(" AND t5.CX_STATE = :CX_STATE ");
				paramMap.put("CX_STATE", CX_STATE);
			}
		}
		sql.append(" LIMIT :START,:LIMIT ");
		paramMap.put("START", start);
		paramMap.put("LIMIT", limit);
		logger.info("queryCzPoscodeTzrxxList sql is " + sql.toString());
		logger.info("queryCzPoscodeTzrxxList paramMap is " + paramMap.toString());
		return this.getNamedParameterJdbcTemplate().queryForList(sql.toString(), paramMap);
	}

	@Override
	public int getCzPoscodeTzrxxCount(JSONObject params) {
		String POS_CODE = params.optString("POS_CODE");
		String POS_OWNER_USER_ID = params.optString("POS_OWNER_USER_ID");
		String POS_OWNER_SJHM = params.optString("POS_OWNER_SJHM");
		String CX_STATE = params.optString("CX_STATE");
		String currentUserId = params.optString("currentUserId");
		StringBuffer sql = new StringBuffer();
		Map<String,String> paramMap = new HashMap<>();
		sql.append(" select COUNT(*) CT  ");
		sql.append("  from t_zhfw_tzrxx t1 ");
		sql.append(" left join t_zhfw_pos_info t2 on t1.POS_CODE = t2.POS_CODE ");
		sql.append(" left join t_zhfw_pos_tzrxx t3 on t1.POS_CODE = t3.POS_CODE ");
		sql.append(" left join t_zhfw_zqzrlb t4 on t1.ZQID = t4.ID ");
		sql.append(" left join t_zhfw_pos_cx_tzrxx t5 on t1.POS_CODE = t5.POS_CODE ");
		sql.append(" where t1.TZ_SOURCE = '01' and t1.TZSY is null ");
		sql.append(" 	and now() <= t4.JXRQ ");
		sql.append(" 	and t2.OPER_USER_ID = :OPER_USER_ID ");
		paramMap.put("OPER_USER_ID", currentUserId);
		if(StringUtils.isNotEmpty(POS_CODE)){
			sql.append(" AND t1.POS_CODE = :POS_CODE ");
			paramMap.put("POS_CODE", POS_CODE);
		}
		if(StringUtils.isNotEmpty(POS_OWNER_USER_ID)){
			sql.append(" AND t2.POS_OWNER_USER_ID = :POS_OWNER_USER_ID ");
			paramMap.put("POS_OWNER_USER_ID", POS_OWNER_USER_ID);
		}
		if(StringUtils.isNotEmpty(POS_OWNER_SJHM)){
			sql.append(" AND t2.POS_OWNER_SJHM LIKE :POS_OWNER_SJHM ");
			paramMap.put("POS_OWNER_SJHM", "%"+POS_OWNER_SJHM+"%");
		}
		if(StringUtils.isNotEmpty(CX_STATE)){
			if("0000".equals(CX_STATE)){//可撤销
				sql.append(" AND t5.CX_STATE IS NULL ");
			}else{
				sql.append(" AND t5.CX_STATE = :CX_STATE ");
				paramMap.put("CX_STATE", CX_STATE);
			}
		}
		logger.info("getCzPoscodeTzrxxCount sql is " + sql.toString());
		logger.info("getCzPoscodeTzrxxCount paramMap is " + paramMap.toString());
		return this.getNamedParameterJdbcTemplate().queryForObject(sql.toString(), paramMap, Integer.class);
	}

	@Override
	public void saveCxsq(JSONObject params) {
		String POS_CODE = params.optString("POS_CODE");
		String currentUserId = params.optString("currentUserId");
		String FB_SQ_CONTENT = params.optString("FB_SQ_CONTENT");
		StringBuffer sql = new StringBuffer();
		Map<String,String> paramMap = new HashMap<>();
		sql.append("INSERT INTO t_zhfw_pos_cx_tzrxx(ID,POS_CODE,FB_SQ_USER_ID,FB_SQ_DATE,FB_SQ_CONTENT,CX_STATE ) ");
		sql.append(" VALUES(UUID(), :POS_CODE, :FB_SQ_USER_ID, NOW(), :FB_SQ_CONTENT, '00') ");
		paramMap.put("POS_CODE", POS_CODE);
		paramMap.put("FB_SQ_USER_ID", currentUserId);
		paramMap.put("FB_SQ_CONTENT", FB_SQ_CONTENT);
		this.getNamedParameterJdbcTemplate().update(sql.toString(), paramMap);
	}

	@Override
	public boolean isHasCxsq(JSONObject params) {
		String POS_CODE = params.optString("POS_CODE");
		StringBuffer sql = new StringBuffer();
		Map<String,String> paramMap = new HashMap<>();
		sql.append(" SELECT COUNT(*) CT FROM t_zhfw_pos_cx_tzrxx WHERE POS_CODE = :POS_CODE ");
		paramMap.put("POS_CODE", POS_CODE);
		int ct = this.getNamedParameterJdbcTemplate().queryForObject(sql.toString(), paramMap, Integer.class);
		if(ct > 0){
			return true;
		}else{
			return false;
		}
	}

	@Override
	public List<Map<String, Object>> queryCzPoscodeTzrxxZbshList(JSONObject params) {
		int start = params.optInt("start");
		int limit = params.optInt("limit");
		String POS_CODE = params.optString("POS_CODE");
		String POS_OWNER_USER_ID = params.optString("POS_OWNER_USER_ID");
		String POS_OWNER_SJHM = params.optString("POS_OWNER_SJHM");
		String CX_STATE = params.optString("CX_STATE");
		String currentUserId = params.optString("currentUserId");
		StringBuffer sql = new StringBuffer();
		Map<String,Object> paramMap = new HashMap<>();
		sql.append(" select t1.ID, t1.USERID, t1.ZQID, t1.TCJE, date_format(t1.TZSJ,'%Y-%m-%d %H:%i:%s') TZSJ, t1.TCZT, t1.HKZT, t1.JXRQ HKRQ, t1.TZSY, t1.TZ_SOURCE ");
		sql.append(" 	 ,t1.POS_CODE, t2.POS_MONEY, t2.POS_BIZ_GYH, t2.POS_OWNER_USER_ID, t2.POS_OWNER_SJHM, t2.POS_PLATFORM, t2.POS_CUSTOMER_SEX  ");
		sql.append("     ,t2.POS_CUSTOMER_SFZ, t2.POS_CUSTOMER_HK_YHMC, t2.POS_CUSTOMER_HK_CARD_NUM, t2.POS_CUSTOMER_LY        ");
		sql.append("     ,t2.OPER_USER_ID, date_format(t2.OPER_DATE,'%Y-%m-%d %H:%i:%s') OPER_DATE, t2.POS_CONTENT             ");
		sql.append("     ,t3.SH_USER_ID, date_format(t3.SH_DATE,'%Y-%m-%d %H:%i:%s') SH_DATE, t3.SH_CONTENT, t3.SH_STATE POS_STATE ");
		sql.append("     ,t4.XMMC, t4.SJSYL, t4.SJSYL_SHOW, t4.JE, t4.QX, t4.JD, t4.FBSJ, t4.ZT, t4.MJQ, t4.JXRQ               ");
		sql.append("	 ,t5.FB_SQ_USER_ID, date_format(t5.FB_SQ_DATE,'%Y-%m-%d %H:%i:%s') FB_SQ_DATE, t5.FB_SQ_CONTENT ");
		sql.append("	 ,t5.ZB_SH_USER_ID, date_format(t5.ZB_SH_DATE,'%Y-%m-%d %H:%i:%s') ZB_SH_DATE, t5.ZB_SH_CONTENT, t5.CX_STATE");
		sql.append("  from t_zhfw_tzrxx t1 ");
		sql.append(" left join t_zhfw_pos_info t2 on t1.POS_CODE = t2.POS_CODE ");
		sql.append(" left join t_zhfw_pos_tzrxx t3 on t1.POS_CODE = t3.POS_CODE ");
		sql.append(" left join t_zhfw_zqzrlb t4 on t1.ZQID = t4.ID ");
		sql.append(" left join t_zhfw_pos_cx_tzrxx t5 on t1.POS_CODE = t5.POS_CODE ");
		sql.append(" where t1.TZ_SOURCE = '01' and t1.TZSY is null ");
		sql.append(" 	and now() <= t4.JXRQ ");
		sql.append(" 	and t2.OPER_USER_ID = :OPER_USER_ID ");
		paramMap.put("OPER_USER_ID", currentUserId);
		if(StringUtils.isNotEmpty(POS_CODE)){
			sql.append(" AND t1.POS_CODE = :POS_CODE ");
			paramMap.put("POS_CODE", POS_CODE);
		}
		if(StringUtils.isNotEmpty(POS_OWNER_USER_ID)){
			sql.append(" AND t2.POS_OWNER_USER_ID = :POS_OWNER_USER_ID ");
			paramMap.put("POS_OWNER_USER_ID", POS_OWNER_USER_ID);
		}
		if(StringUtils.isNotEmpty(POS_OWNER_SJHM)){
			sql.append(" AND t2.POS_OWNER_SJHM LIKE :POS_OWNER_SJHM ");
			paramMap.put("POS_OWNER_SJHM", "%"+POS_OWNER_SJHM+"%");
		}
		if(StringUtils.isNotEmpty(CX_STATE)){
			sql.append(" AND t5.CX_STATE = :CX_STATE ");
			paramMap.put("CX_STATE", CX_STATE);
		}else{
			sql.append("  AND t5.CX_STATE IS NOT NULL ");
		}
		sql.append(" LIMIT :START,:LIMIT ");
		paramMap.put("START", start);
		paramMap.put("LIMIT", limit);
		logger.info("queryCzPoscodeTzrxxList sql is " + sql.toString());
		logger.info("queryCzPoscodeTzrxxList paramMap is " + paramMap.toString());
		return this.getNamedParameterJdbcTemplate().queryForList(sql.toString(), paramMap);
	}

	@Override
	public int getCzPoscodeTzrxxZbshCount(JSONObject params) {
		String POS_CODE = params.optString("POS_CODE");
		String POS_OWNER_USER_ID = params.optString("POS_OWNER_USER_ID");
		String POS_OWNER_SJHM = params.optString("POS_OWNER_SJHM");
		String CX_STATE = params.optString("CX_STATE");
		String currentUserId = params.optString("currentUserId");
		StringBuffer sql = new StringBuffer();
		Map<String,String> paramMap = new HashMap<>();
		sql.append(" select COUNT(*) CT  ");
		sql.append("  from t_zhfw_tzrxx t1 ");
		sql.append(" left join t_zhfw_pos_info t2 on t1.POS_CODE = t2.POS_CODE ");
		sql.append(" left join t_zhfw_pos_tzrxx t3 on t1.POS_CODE = t3.POS_CODE ");
		sql.append(" left join t_zhfw_zqzrlb t4 on t1.ZQID = t4.ID ");
		sql.append(" left join t_zhfw_pos_cx_tzrxx t5 on t1.POS_CODE = t5.POS_CODE ");
		sql.append(" where t1.TZ_SOURCE = '01' and t1.TZSY is null ");
		sql.append(" 	and now() <= t4.JXRQ ");
		sql.append(" 	and t2.OPER_USER_ID = :OPER_USER_ID ");
		paramMap.put("OPER_USER_ID", currentUserId);
		if(StringUtils.isNotEmpty(POS_CODE)){
			sql.append(" AND t1.POS_CODE = :POS_CODE ");
			paramMap.put("POS_CODE", POS_CODE);
		}
		if(StringUtils.isNotEmpty(POS_OWNER_USER_ID)){
			sql.append(" AND t2.POS_OWNER_USER_ID = :POS_OWNER_USER_ID ");
			paramMap.put("POS_OWNER_USER_ID", POS_OWNER_USER_ID);
		}
		if(StringUtils.isNotEmpty(POS_OWNER_SJHM)){
			sql.append(" AND t2.POS_OWNER_SJHM LIKE :POS_OWNER_SJHM ");
			paramMap.put("POS_OWNER_SJHM", "%"+POS_OWNER_SJHM+"%");
		}
		if(StringUtils.isNotEmpty(CX_STATE)){
			sql.append(" AND t5.CX_STATE = :CX_STATE ");
			paramMap.put("CX_STATE", CX_STATE);
		}else{
			sql.append("  AND t5.CX_STATE IS NOT NULL ");
		}
		logger.info("getCzPoscodeTzrxxCount sql is " + sql.toString());
		logger.info("getCzPoscodeTzrxxCount paramMap is " + paramMap.toString());
		return this.getNamedParameterJdbcTemplate().queryForObject(sql.toString(), paramMap, Integer.class);
	}

	@Override
	public boolean isReadyCxsh(JSONObject params) {
		String POS_CODE = params.optString("POS_CODE");
		StringBuffer sql = new StringBuffer();
		Map<String,String> paramMap = new HashMap<>();
		sql.append(" SELECT COUNT(*) CT FROM t_zhfw_pos_cx_tzrxx WHERE CX_STATE = '00' AND POS_CODE = :POS_CODE ");
		paramMap.put("POS_CODE", POS_CODE);
		logger.info("isReadyCxsh sql is : " + sql.toString());
		logger.info("isReadyCxsh paramMap is : " + paramMap.toString());
		int ct = this.getNamedParameterJdbcTemplate().queryForObject(sql.toString(), paramMap, Integer.class);
		if(ct > 0){
			return true;
		}else{
			return false;
		}
	}

	@Override
	public void saveCxsh(JSONObject params) {
		String POS_CODE = params.optString("POS_CODE");
		String CX_STATE = params.optString("CX_STATE");
		String currentUserId = params.optString("currentUserId");
		String ZB_SH_CONTENT = params.optString("ZB_SH_CONTENT");
		StringBuffer sql = new StringBuffer();
		Map<String,String> paramMap = new HashMap<>();
		sql.append(" UPDATE t_zhfw_pos_cx_tzrxx  ");
		sql.append(" SET ZB_SH_USER_ID =:ZB_SH_USER_ID, ZB_SH_DATE = NOW(), ZB_SH_CONTENT =:ZB_SH_CONTENT, CX_STATE =:CX_STATE ");
		sql.append(" WHERE POS_CODE = :POS_CODE ");
		paramMap.put("POS_CODE", POS_CODE);
		paramMap.put("CX_STATE", CX_STATE);
		paramMap.put("ZB_SH_USER_ID", currentUserId);
		paramMap.put("ZB_SH_CONTENT", ZB_SH_CONTENT);
		this.getNamedParameterJdbcTemplate().update(sql.toString(), paramMap);
	
	}
	
	
	
}
